package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 * implement all TeacherQuery  handler
 * */

public class TeacherQuery extends Query{

	public TeacherQuery() {
		super();
	}
	/**
	 * getTeacherById
	 * @param idTeacher
	 * @return
	 * @throws SQLException
	 */
	public Teacher getTeacherById(int idTeacher) throws SQLException{
		User user = new UserQuery().getUserByIdUser(idTeacher);
		setQuery("SELECT * FROM teacher WHERE idteacher ='"+idTeacher+"'");
		ResultSet rs = execQuery();
		Teacher teacher = null;
		if (rs.next()){
			teacher = new Teacher(user.getIdUser(), user.getUserName(), user.getPassword(), user.getPermission(),
					user.isOnline(), user.getFirstName(), user.getLastName(), rs.getInt(2), rs.getInt(3));
		}
		return teacher;
	}
	/**
	 * 
	 * @param tUnit
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<Teacher> getTeachersByTUnit(int tUnit) throws SQLException{
		ArrayList<Teacher> array = new ArrayList<Teacher>();
		if(tUnit==0)
			setQuery("SELECT idteacher FROM teacher");
		else setQuery("SELECT idteacher FROM teachertunit WHERE tunit='"+tUnit+"'");
		ResultSet rs = execQuery();
		while (rs.next()) array.add(getTeacherById(rs.getInt(1)));
		return array;
	}
	/**
	 * 
	 * @return teacher table
	 * @throws SQLException
	 */
	/**
	 * add hour to teacher 
	 * @param idTeacher
	 * @param hours
	 * @throws SQLException
	 */
	public void addHours(int idTeacher, int hours) throws SQLException{
		int hour = hours + getTeacherById(idTeacher).getHours();
		setQuery("UPDATE teacher SET hour=? WHERE idteacher=?");
		PreparedStatement query = getPS();
		query.setInt(1, hour);
		query.setInt(2, idTeacher);
		query.executeUpdate();
		query.close();
	}
	/**
	 * 
	 * @param idTeacher
	 * @param hours
	 * @throws SQLException
	 */
	public void decHours(int idTeacher, int hours) throws SQLException{
		int hour = getTeacherById(idTeacher).getHours()-hours;
		setQuery("UPDATE teacher SET hour=? WHERE idteacher=?");
		PreparedStatement query = getPS();
		query.setInt(1, hour);
		query.setInt(2, idTeacher);
		query.executeUpdate();
		query.close();
	}
	/**
	 * clear teacher hour
	 * @throws SQLException
	 */
	public void resetHours() throws SQLException{
		ArrayList<Teacher> array = getTeachersByTUnit(0);
		for (Teacher teacher: array){
			setQuery("UPDATE teacher SET hour='0' WHERE idteacher=?");
			PreparedStatement query = getPS();
			query.setInt(1, teacher.getIdUser());
			query.executeUpdate();
			query.close();
		}
	}
	/**
	 * 
	 * @param tUnit
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<ReportIF> getTeachers() throws SQLException{
		ArrayList<ReportIF> array = new ArrayList<ReportIF>();
		setQuery("SELECT idteacher FROM teacher");
		ResultSet rs = execQuery();
		while (rs.next()) array.add(getTeacherById(rs.getInt(1)));
		return array;
	}

}
